package orest.front.expert;

import java.sql.Connection;

import net.ssta.core.base.SQLBuilderBase;
import net.ssta.core.web.RequestKit;



/**
 * 
 * @ClassName:
 * @Description: 
 * @author: 
 * @date: 
 * 
 */
public class ExpertSql extends SQLBuilderBase
{
    /*
     * m_builder : 单例实例
     */
    private static ExpertSql m_builder = new ExpertSql();
    
    /**
     * @Title: getSQLBuilder
     * @Description: 获取实例
     * 
     * @param conn 数据库连
     * @return 单例实例
     */
    public static ExpertSql getSQLBuilder(Connection conn)
    {
        return m_builder;
    }
    
    
    /**
     * 科研单位推荐状态修改
     * 
     * @param strFormTo
     * @param title
     * @param content
     * @return
     */
    public static String changeUnitStatus(String unitUuids,String fieldname,String status)
    {
        StringBuffer sb = new StringBuffer(); 
        sb.append("UPDATE T_UNIT SET ").append(fieldname);
        sb.append(" = '").append(status).append("'");
        String expertUuidArr[] = unitUuids.split(",");
    	sb.append(" WHERE UNITID IN (");
        for(int i=0;i<expertUuidArr.length;i++){
        	sb.append("'").append(expertUuidArr[i]).append("'");
        	if(i<expertUuidArr.length-1){
        		sb.append(",");
        	}
        } 
	    sb.append(")");
        return sb.toString();
    }
    
    
    
    
    /**
     * 科技专家列表
     * 
     * @param strFormTo
     * @param title
     * @param content
     * @return
     */
    public static String getExpertList(RequestKit requestKit)
    {
        StringBuffer sb = new StringBuffer(); 
        sb.append(" SELECT T.EXPERTID,T.ITEM_180 EXPERTNAME,V.OPTIONNAME NEWENERGYCLASSNAME,T.WORKSPECIALITY FROM T_EXPERT T ");
        sb.append(" LEFT JOIN  (SELECT S.OPTIONVALUE,S.OPTIONNAME FROM T_FIELDOPTION S ");
        sb.append(" WHERE S.TABLENAME='common' AND S.FIELDNAME='NewEnergyClass') V  ");
        sb.append(" ON T.NEWENERGYCLASS1 = V.OPTIONVALUE ");
        return sb.toString();
    }
    
    
    
    
    
    /**
     * 获得推荐专家列表
     * 
     * @param strFormTo
     * @param title
     * @param content
     * @return
     */
    public static String getRecommendExpertList(RequestKit requestKit)
    {
    	StringBuffer sb = new StringBuffer(); 
        sb.append(" SELECT T.EXPERTID,T.ITEM_180 EXPERTNAME,T.EXPINTRO,T.PHOTO ,V.OPTIONNAME NEWENERGYCLASSNAME,T.WORKSPECIALITY FROM T_EXPERT T ");
        sb.append(" LEFT JOIN  (SELECT S.OPTIONVALUE,S.OPTIONNAME FROM T_FIELDOPTION S ");
        sb.append(" WHERE S.TABLENAME='common' AND S.FIELDNAME='NewEnergyClass') V  ");
        sb.append(" ON T.NEWENERGYCLASS1 = V.OPTIONVALUE ");
        
        sb.append(" WHERE 1=1 ");
        //当showType为HomepageShow时推荐到首页，为ChannelShow时推荐到频道
        String showType = requestKit.getParameter("showType", "");
        if(showType.equals("HomepageShow")){
        	sb.append(" AND T.HOMEPAGESHOW = '1'");
        	sb.append(" ORDER BY T.HOMEPAGESORTNUM");
        }
        if(showType.equals("ChannelShow")){
        	sb.append(" AND T.CHANNELSHOW = '1'");
        	sb.append(" ORDER BY T.CHANNELSORTNUM");
        }
        return sb.toString();
    }
    
}
